﻿Public Class clsAutoPlantingDB
    Private m_cEquipMat As New clsDbEquipMat
    Private _PLT_EQUIP_NO As String

    Public Property PLT_EQUIP_NO As String
        Get
            Return _PLT_EQUIP_NO
        End Get
        Set(value As String)
            _PLT_EQUIP_NO = value
        End Set
    End Property

    ''' <summary>
    ''' 电镀完成的列表
    ''' </summary>
    ''' <returns>电镀完成的列表</returns>
    Public Function getWorkList() As DataTable

        Dim szSql As String = ""
        Dim dtList As DataTable
        Dim dtDetail As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("LOT_NO")
        dt.Columns.Add("RACK_NO")
        dt.Columns.Add("CHANGE_STATUS")
        dt.Columns.Add("CHANGE_STATUS_NAME")
        dt.Columns.Add("FLOW_NO")
        dt.Columns.Add("IN_COUNT")
        dt.Columns.Add("PRODUCT")
        dt.Columns.Add("PKG_NO")
        dt.Columns.Add("UPDATE_TIME")
        dt.Columns.Add("TYPE")

        szSql = "SELECT * from ("
        szSql &= "SELECT DISTINCT LOT_NO,update_time "
        szSql &= "FROM PER.L_SF_WORK_PLANT_RSLT "
        szSql &= "WHERE (TYPE=0 or TYPE=1) and KOUTEI_CD=? "
        szSql &= "order by update_time "
        szSql &= ") where  rownum<= 15"

        dtList = DBacs.ExecuteSql(szSql, -1, _PLT_EQUIP_NO)

        If IsNothing(dtList) Then
            dtList = New DataTable
        End If

        If IsNothing(dtList) = False Then

            szSql = "SELECT * FROM PER.L_SF_WORK_PLANT_RSLT WHERE (TYPE=0 or TYPE=1) order by update_time"

            dtDetail = DBacs.ExecuteSql(szSql, -1)

            If IsNothing(dtDetail) Then
                dtDetail = New DataTable
            End If

            If IsNothing(dtDetail) = False Then

                For Each drList As DataRow In dtList.Rows
                    Dim drDetails As DataRow() = dtDetail.Select("LOT_NO='" & drList("LOT_NO") & "'")
                    Dim dr As DataRow = dt.Rows.Add

                    For Each drDetail As DataRow In drDetails
                        dr("LOT_NO") = drDetail("LOT_NO")
                        dr("UPDATE_TIME") = drDetail("UPDATE_TIME")
                        dr("TYPE") = IIf(drDetail("type") = 0, "未着工", "着工中")
                        Select Case drDetail("MNF_CND_TAG")
                            Case "ACCEPTED MS RACK NO"
                                dr("RACK_NO") = drDetail("ACTUAL_MODEL")
                            Case "CHANGE  STATUS"
                                dr("CHANGE_STATUS") = drDetail("ACTUAL_MODEL")
                                Dim strType = drDetail("ACTUAL_MODEL")
                                For i As Integer = 0 To strType.ToString.Length - 1
                                    If strType(i) = "1" Then
                                        If i = 0 Then dr("CHANGE_STATUS_NAME") &= " 再生品 "
                                        If i = 1 Then dr("CHANGE_STATUS_NAME") &= " 车品 "
                                        If i = 2 Then dr("CHANGE_STATUS_NAME") &= " 工实品 "
                                        If i = 3 Then dr("CHANGE_STATUS_NAME") &= " JAM LOT "
                                        If i = 4 Then dr("CHANGE_STATUS_NAME") &= " 首末LOT "
                                        If i = 5 Then dr("CHANGE_STATUS_NAME") &= " PKG切替 "
                                        If i = 6 Then dr("CHANGE_STATUS_NAME") &= " 镀种切替 "
                                        If i = 7 Then dr("CHANGE_STATUS_NAME") &= " 数据异常 "
                                        If i = 8 Then dr("CHANGE_STATUS_NAME") &= " 其它 "
                                    End If
                                Next
                            Case "FLOW NO"
                                dr("FLOW_NO") = drDetail("ACTUAL_MODEL")
                            Case "OUT COUNT"
                                dr("IN_COUNT") = drDetail("ACTUAL_MODEL")
                            Case "PKG NO"
                                dr("PKG_NO") = drDetail("ACTUAL_MODEL")
                            Case "PRODUCT"
                                dr("PRODUCT") = drDetail("ACTUAL_MODEL")
                        End Select
                    Next

                Next

            End If

        End If
        getWorkList = dt

    End Function

    ''' <summary>
    ''' 进行上下顺序调整
    ''' </summary>
    ''' <param name="lot1">Lot1的LotNo</param>
    ''' <param name="ut1">Lot1的updateTime时间</param>
    ''' <param name="lot2">Lot2的LotNo</param>
    ''' <param name="ut2">Lot2的updateTime时间</param>
    ''' <returns>成功为1，失败为-1</returns>
    Public Function changeUpdateTime(lot1 As String, ut1 As String, lot2 As String, ut2 As String) As Integer
        Dim rtn As Integer = -1
        Try

            'トランザクション開始
            DBacs.BeginTransaction()

            Dim szSql As String = ""
            szSql &= " update PER.L_SF_WORK_PLANT_RSLT"
            szSql &= " set update_time= ? "
            szSql &= " WHERE lot_no= ? "


            If DBacs.ExecuteUpdateSql(szSql, -1, ut2, lot1) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If
            If DBacs.ExecuteUpdateSql(szSql, -1, ut1, lot2) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If
            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()

            Return -1
        End Try
    End Function

    Public Function WorkCancel(_lot As String) As Integer
        Dim szSql As String = ""
        DBacs.BeginTransaction()
        Try

            szSql &= " update PER.L_SF_WORK_PLANT_RSLT "
            szSql &= " set type= '0' "
            szSql &= " WHERE lot_no= ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If
            '更新PER.L_TAG_LOTDATA表里的数据 的状态
            szSql = " update PER.L_TAG_LOTDATA "
            szSql &= " set STATUS = 0 "
            szSql &= " where LOT_NO = ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If
            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()
            Return -1
        End Try

    End Function

    ''' <summary>
    ''' 着工完成后的数据库操作
    ''' </summary>
    ''' <param name="_lot">LotNo</param>
    ''' <param name="_EQUIPNO">设备No.</param>
    ''' <param name="_KOUTEI_CD">工程号</param>
    ''' <returns>成功：1，失败：-1</returns>
    Public Function workStart(_lot As String, _EQUIPNO As String, _KOUTEI_CD As String, dtHeader As DataTable) As Integer
        Dim szSql As String = ""

        DBacs.BeginTransaction()
        Try
            '更新Autoplanting表里的数据 的状态
            szSql &= " update PER.L_SF_WORK_PLANT_RSLT "
            szSql &= " set type= '1' "
            szSql &= " WHERE lot_no= ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If

            '更新PER.L_TAG_LOTDATA表里的数据 的状态
            szSql = " update PER.L_TAG_LOTDATA "
            szSql &= " set STATUS = 1 ,EQUIPNO = ? "
            szSql &= " where LOT_NO = ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _EQUIPNO, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If

            '插入着工履历header
            Dim intMax As Integer
            m_cEquipMat.SaveHeader(dtHeader, intMax, _lot, _EQUIPNO, 1, Now())

            '插入着工履历
            szSql = "  insert into mat.L_SF_WORK_RSLT_" & _KOUTEI_CD & " value ( "
            szSql &= " Select LOT_NO,KOUTEI_CD,TYPE, "
            szSql &= intMax & ", MNF_CND_TAG, MNF_CND_TAG_FLG, SEQ, "
            szSql &= " MNF_CONDITION, ACTUAL_MODEL, MANAGEMENT_NO, MATERIAL_LOT, LIMIT_TIME, (select SYSDATE from dual)  "
            szSql &= " From PER.L_SF_WORK_PLANT_RSLT Where LOT_NO =?) "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If

            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()
            Return -1
        End Try
    End Function

    Public Function finishWork(_lot As String, _EQUIPNO As String, _KOUTEI_CD As String, _NEXT_KOUTEI_CD As String, dtHeader As DataTable)
        Dim szSql As String = ""

        Try

            DBacs.BeginTransaction()
            '更新Autoplanting表里的数据 的状态
            szSql &= " update PER.L_SF_WORK_PLANT_RSLT "
            szSql &= " set type= '2'  "
            szSql &= " WHERE lot_no= ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If


            '更新PER.L_TAG_LOTDATA表里的数据 的状态
            szSql = " update PER.L_TAG_LOTDATA "
            szSql &= " set STATUS = 0 ,EQUIPNO = '' ,KOUTEI_CODE=?"
            szSql &= " where LOT_NO = ? "
            If DBacs.ExecuteUpdateSql(szSql, -1, _NEXT_KOUTEI_CD, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If
            '插入着工履历header
            Dim intMax As Integer
            m_cEquipMat.SaveHeader(dtHeader, intMax, _lot, _EQUIPNO, 3, Now())

            '插入完工履历
            szSql = "  insert into mat.L_SF_WORK_RSLT_" & _KOUTEI_CD & " value ( "
            szSql &= " Select LOT_NO,KOUTEI_CD,3, "
            szSql &= intMax & ", MNF_CND_TAG, MNF_CND_TAG_FLG, SEQ, "
            szSql &= " MNF_CONDITION, ACTUAL_MODEL, MANAGEMENT_NO, MATERIAL_LOT, LIMIT_TIME, (select SYSDATE from dual)  "
            szSql &= " From PER.L_SF_WORK_PLANT_RSLT Where LOT_NO =?) "
            If DBacs.ExecuteUpdateSql(szSql, -1, _lot) = -2 Then
                DBacs.RollbackTransaction()
                Return -1
            End If

            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()
            Return -1
        End Try


    End Function

    Public Function saveError(objWG As cmd_WG, _lotNo As String) As Integer
        Dim szSql As String = "insert into PER.L_AP_ERROR_HIST(LOT_NO,ERROR_ITEM,ERROR_NUM,UPDATE_TIME) values (?,?,?,(SELECT SYSDATE FROM DUAL))"
        Try

            DBacs.BeginTransaction()
            'delete OLD Data
            If objWG.PL1A1 <> 0 Or objWG.PL1B1 <> 0 Or objWG.PL1C1 <> 0 Or objWG.PL1D1 <> 0 Or objWG.PL1E1 <> 0 Or
            objWG.PL1F1 <> 0 Or objWG.PL1G1 <> 0 Or objWG.PL1H1 <> 0 Or objWG.PL1I1 <> 0 Then
                Dim _sql As String = "delete PER.L_AP_ERROR_HIST where LOT_NO=? "
                If DBacs.ExecuteUpdateSql(_sql, -1, _lotNo) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1A1 <> 0 Then

                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1A1", objWG.PL1A1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1B1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1B1", objWG.PL1B1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1C1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1C1", objWG.PL1C1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1D1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1D1", objWG.PL1D1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1E1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1E1", objWG.PL1E1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1F1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1F1", objWG.PL1F1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1G1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1G1", objWG.PL1G1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1H1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1H1", objWG.PL1H1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            If objWG.PL1I1 <> 0 Then
                If DBacs.ExecuteUpdateSql(szSql, -1, _lotNo, "PL1I1", objWG.PL1I1) = -2 Then
                    DBacs.RollbackTransaction()
                    Return -1
                End If
            End If
            DBacs.CommitTransaction()
            Return 1
        Catch ex As Exception
            DBacs.RollbackTransaction()
            Return -1
        End Try
    End Function

    Public Function getEquipInfo(_equip_no As String) As DataTable
        Dim szSql As String = ""
        szSql = "SELECT * FROM PER.T_AP_EQUIP where equip_no=?"

        getEquipInfo = DBacs.ExecuteSql(szSql, -1, _equip_no)
    End Function

    Public Function delete(lot As String) As Integer
        Dim rtn As Integer = -1
        Try


            Dim szSql As String = ""
            szSql &= " update PER.L_SF_WORK_PLANT_RSLT"
            szSql &= " set type=4 "
            szSql &= " WHERE lot_no= ? "

            DBacs.ExecuteUpdateSql(szSql, -1, lot)

            Return 1
        Catch ex As Exception
            Return -1
        End Try
    End Function
End Class
